<?php
/**
 * Created by PhpStorm.
 * User: xuanbang.wen
 * Date: 2020/4/29
 * Time: 14:07
 */

namespace App\Command\ExportFile;
use App\Web\Model\CaseFlow;
use PhpOffice\PhpSpreadsheet;
use EchoPHP\Facades\Log as Log;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\DataType;


class ReportExportHelper {
    protected $template;
    protected $scheduleTemplate;
    protected $weeklyTemplate;

    private $workbook;

    private $chargewayDict = [
      '1'=>'事务性工作',
      '2'=>'合格简历',
      '3'=>'人员面试',
      '4'=>'招聘会',
      '5'=>'offer',
      '6'=>'到岗',
      '7'=>'到岗+保证期',
    ];

    private $salaryTypeDict = [
        '1'=>'年薪比例',
        '2'=>'月薪系数',
        '3'=>'固定金额'
    ];

    private $fieldTranslate = [
        'chargeway'=>'交付方式',
        'casenum'=>'职位数',
        'mycasenum'=>'我的职位数',
        'vacancy'=>'需求人数',
        'myvacancy'=>'我的需求人数',
        'recommend'=>'本周推荐量',
        'myrecommend'=>'我的本周推荐量',
        'targetnum'=>'本周推荐量目标',
        'mytargetnum'=>'我的本周推荐量目标',
        'completion'=>'已交付人数',
        'mycompletion'=>'我的已交付人数',
        'chargewaytargetnum'=>'交付目标',
        'complaterate'=>'完成率',
    ];

    public function __construct() {
        $this->template = [
            'path' => ROOT_PATH . '/app/Command/ExportFile/resource/reporttemplate.xls',
            'summaryCell' => 'A1',
            'fields'=>['chargeway', 'casenum','vacancy', 'completion','chargewaytargetnum','complaterate','recommend','targetnum','mycasenum','myvacancy','myrecommend','mycompletion','mytargetnum']
        ];

        $this->scheduleTemplate = [
            'path' => ROOT_PATH . '/app/Command/ExportFile/resource/scheduletemplate.xls',
            'summaryCell' => 'A1',
        ];

        $this->weeklyTemplate = [
            'path' => ROOT_PATH . '/app/Command/ExportFile/resource/weeklytemplate.xls',
            'summaryCell' => 'A1',
        ];

        $this->monthlyTemplate = [
            'path' => ROOT_PATH . '/app/Command/ExportFile/resource/monthlytemplate.xls',
            'summaryCell' => 'A1',
        ];
    }

    public function export($data, $extraFields){
        if (empty($data)){
            return response()->json([
                'status' => false,
                'message' => '无可导出数据',
            ]);
        }
        $extraFields = explode(',', $extraFields);
        $extraFields []= 'chargeway';
        //有已交付人数和交付目标时计算完成率
        if (in_array('completion', $extraFields) && in_array('chargewaytargetnum', $extraFields)){
            $extraFields[] = 'complaterate';
        }
        $extraFields = array_unique(array_intersect($extraFields, $this->template['fields']));
        $this->workbook = PhpSpreadsheet\IOFactory::load($this->template['path']);
        $indexSheet = $this->workbook->getSheet(0);
        $rowIndex = 3;

        //可选字段表头
        $i = 0;
        foreach ($this->template['fields'] as $extraField){
            if (in_array($extraField, $extraFields)){
                $indexSheet->setCellValueByColumnAndRow($i + 6, 2, $this->fieldTranslate[$extraField]);
                $i++;
            }
        }
        //输出内容
        foreach ($data as $key1=>$project){
            $indexSheet->setCellValueByColumnAndRow(1, $rowIndex, $project['projectid']);
            $indexSheet->setCellValueByColumnAndRow(2, $rowIndex, $project['projectname']);
            $charger = [];
            if ($project['opr'] && $project['opr'][0]['ischarger'] == 1){
                $charger = $project['opr'][0];
            }
            $indexSheet->setCellValueByColumnAndRow(3, $rowIndex, $charger['cname'] ?? '');
            $indexSheet->setCellValueByColumnAndRow(4, $rowIndex, implode(',', array_column($project['opr'], 'cname')));
            $indexSheet->setCellValueByColumnAndRow(5, $rowIndex, $project['subname']);
            //合并项目单元格
            if (count($project['report']) > 1){
                $endRowIndex = $rowIndex + count($project['report']) -1;
                $indexSheet->mergeCellsByColumnAndRow(1, $rowIndex, 1, $endRowIndex);
                $indexSheet->mergeCellsByColumnAndRow(2, $rowIndex, 2, $endRowIndex);
                $indexSheet->mergeCellsByColumnAndRow(3, $rowIndex, 3, $endRowIndex);
                $indexSheet->mergeCellsByColumnAndRow(4, $rowIndex, 4, $endRowIndex);
                $indexSheet->mergeCellsByColumnAndRow(5, $rowIndex, 5, $endRowIndex);
            }
            foreach ($project['report'] as $chargeway){
                $complateRate = $chargeway['chargewaytargetnum'] == 0 ? '':ceil($chargeway['completion']*100/$chargeway['chargewaytargetnum']) . '%';
                $chargewayData = [
                    'chargeway'=>$this->chargewayDict[$chargeway['chargeway']],
                    'casenum'=>$chargeway['casenum'],
                    'vacancy'=>$chargeway['vacancy'],
                    'completion'=>$chargeway['completion'],
                    'chargewaytargetnum'=>$chargeway['chargewaytargetnum'],
                    'complaterate'=>$complateRate,
                    'recommend'=>$chargeway['recommend'],
                    'targetnum'=>$chargeway['targetnum'],
                    'mycasenum'=>$chargeway['mycasenum'],
                    'myvacancy'=>$chargeway['myvacancy'],
                    'myrecommend'=>$chargeway['myrecommend'],
                    'mycompletion'=>$chargeway['mycompletion'],
                    'mytargetnum'=>$chargeway['mytargetnum'],
                ];
                $fieldColumn = 6;
                foreach ($chargewayData as $key=>$item){
                    if (in_array($key, $extraFields)){
                        $indexSheet->setCellValueExplicitByColumnAndRow($fieldColumn, $rowIndex, $item, DataType::TYPE_STRING);
                        $fieldColumn++;
                    }
                }
                $rowIndex++;
            }
        }
        $summary = '共导出' . count($data) . '个项目。';
        $indexSheet->setCellValue($this->template['summaryCell'], $summary);
        $writer = IOFactory::createWriter($this->workbook, 'Xlsx');
        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
        }, '报表数据.xlsx', ['Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8']);
    }

    /**
     * Notes:导出进度报表
     * @param $data
     * @param $projectId
     * @return \EchoPHP\Http\Response\StreamedResponse
     * @throws PhpSpreadsheet\Exception
     * @throws PhpSpreadsheet\Reader\Exception
     * @throws PhpSpreadsheet\Writer\Exception
     */
    public function exportSchedule($data, $projectId){
        $this->workbook = PhpSpreadsheet\IOFactory::load($this->scheduleTemplate['path']);
        $indexSheet = $this->workbook->getSheet(0);
        $projectFlow = (new CaseFlow())->getProjectFlowFull($projectId);
        $startColumn = 7;
        //记录流程code对应的列
        $flowatstatusColumnMap = [];
        //流程节点表头
        foreach ($projectFlow as $flowatCode=>$flowat){
            if (!$flowat['isnecessary']){
                continue;
            }
            $start = $startColumn;
            $indexSheet->setCellValueByColumnAndRow($startColumn, 1, $flowat['name']);
            foreach ($flowat['child'] as $flowstatusCode=>$flowatstatus){
                $indexSheet->setCellValueByColumnAndRow($startColumn, 2, $flowatstatus['name']);
                $flowatstatusColumnMap[$flowatCode . $flowstatusCode] = $startColumn;
                $startColumn++;
            }
            $indexSheet->mergeCellsByColumnAndRow($start, 1, $startColumn -1, 1);
        }
        $maxRowIndex = 3;
        foreach ($data as $datum){
            $maxRowIndex += (count($datum['oprlist'])+1);
        }
        //合计
        $maxColumnIndex = 6 + count($flowatstatusColumnMap);
        $indexSheet->setCellValue('D3', '=SUM(D4:D'. $maxRowIndex.')');
        for ($columnIndex = 6; $columnIndex <= $maxColumnIndex; $columnIndex++){
            $column = PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnIndex);
            //计算时包括了"小计"，要除2
            $indexSheet->setCellValue($column . '3', '=(SUM('.$column.'4:'.$column . $maxRowIndex.'))/2');
        }
        //输出内容
        $startRow = 4;
        foreach ($data as $key=>$datum){
            $start = $startRow;
            $indexSheet->setCellValueByColumnAndRow(1, $startRow, $datum['caseid']);
            $indexSheet->setCellValueExplicitByColumnAndRow(2, $startRow, $datum['casename'], DataType::TYPE_STRING);
            $indexSheet->setCellValueExplicitByColumnAndRow(3, $startRow, $this->chargewayDict[$datum['chargeway']], DataType::TYPE_STRING);
            $indexSheet->setCellValueByColumnAndRow(4, $startRow, $datum['vacancy']);
            $count = 0;
            $countMap = [];
            foreach ($datum['oprlist'] as $caseopr){
                $count += $caseopr['completion'];
                $indexSheet->setCellValueExplicitByColumnAndRow(5, $startRow, $caseopr['oprname'], DataType::TYPE_STRING);
                $indexSheet->setCellValueByColumnAndRow(6, $startRow, $caseopr['completion']);
                foreach ($caseopr['schuedule'] as $schuedule){
                    $indexSheet->setCellValueByColumnAndRow($flowatstatusColumnMap[$schuedule['flowatstatus']], $startRow, $schuedule['count']);
                    $countMap[$schuedule['flowatstatus']] = ($countMap[$schuedule['flowatstatus']] ?? 0) + $schuedule['count'];
                }
                $startRow++;
            }
            //小计
            $indexSheet->setCellValueByColumnAndRow(5, $startRow, '小计');
            $indexSheet->setCellValueByColumnAndRow(6, $startRow, $count);
            foreach ($countMap as $flowatCode=>$item){
                $indexSheet->setCellValueByColumnAndRow($flowatstatusColumnMap[$flowatCode], $startRow, $item);
            }
            //合并单元格
            $indexSheet->mergeCellsByColumnAndRow(1, $start, 1, $startRow);
            $indexSheet->mergeCellsByColumnAndRow(2, $start, 2, $startRow);
            $indexSheet->mergeCellsByColumnAndRow(3, $start, 3, $startRow);
            $indexSheet->mergeCellsByColumnAndRow(4, $start, 4, $startRow);
            $startRow++;
        }
        $summary = '共导出' . count($data) . '个职位。';
        $indexSheet->setCellValue($this->template['summaryCell'], $summary);
        $writer = IOFactory::createWriter($this->workbook, 'Xlsx');
        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
        }, '进度报表数据.xlsx', ['Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8']);
    }

    /**
     * Notes:导出周报表
     * @param $data
     * @param $oprIds
     * @return \EchoPHP\Http\Response\StreamedResponse
     * @throws PhpSpreadsheet\Exception
     * @throws PhpSpreadsheet\Reader\Exception
     * @throws PhpSpreadsheet\Writer\Exception
     */
    public function exportWeekLyRecommend($data, $oprIds){
        $this->workbook = PhpSpreadsheet\IOFactory::load($this->weeklyTemplate['path']);
        $indexSheet = $this->workbook->getSheet(0);
        //样式
        $indexSheet->getDefaultColumnDimension()->setWidth(12);
        $styleArray = [
            //居中
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        //用户列表
        $startColumn = 10;
        $userColumnMap = [];
        foreach ($data['userlist'] as $user){
            if (!in_array($user['userid'], $oprIds)){
                continue;
            }
            $indexSheet->setCellValueByColumnAndRow($startColumn, 1, $user['cname']);
            $indexSheet->setCellValueByColumnAndRow($startColumn, 2, '推荐量目标');
            $indexSheet->setCellValueByColumnAndRow($startColumn + 1, 2, '推荐量');
            $indexSheet->mergeCellsByColumnAndRow($startColumn, 1, $startColumn+1, 1);
            $userColumnMap[$user['userid']] = $startColumn;
            $startColumn += 2;
        }

        //数据填充
        $startRow = 3;
        foreach ($data['weeklyreport'] as $item){
            $targetCount = $recommendCount = 0;
            $date = date('Y', strtotime($item['date'])).'年第'.$item['weeknum'].'周（'.date('Y-m-d',strtotime($item['date'])).'到'.date('Y-m-d',strtotime('+6 days', strtotime($item['date']))).'）';
            $indexSheet->setCellValueByColumnAndRow(1, $startRow, $date);
            $indexSheet->setCellValueByColumnAndRow(2, $startRow, $item['caseid']);
            $indexSheet->setCellValueExplicitByColumnAndRow(3, $startRow, $item['casename'], DataType::TYPE_STRING);
            $indexSheet->setCellValueExplicitByColumnAndRow(4, $startRow, $this->chargewayDict[$item['chargeway']], DataType::TYPE_STRING);
            $indexSheet->setCellValueByColumnAndRow(5, $startRow, $item['vacancy']);
            $indexSheet->setCellValueByColumnAndRow(6, $startRow, $this->salaryTypeDict[$item['salarytype']]);
            $indexSheet->setCellValueByColumnAndRow(7, $startRow, $item['completion']);

            foreach ($item['userreport'] as $value){
                if (!in_array($value['oprid'], $oprIds)){
                    continue;
                }
                $indexSheet->setCellValueByColumnAndRow($userColumnMap[$value['oprid']], $startRow, $value['targetnum']);
                $indexSheet->setCellValueByColumnAndRow($userColumnMap[$value['oprid']] + 1, $startRow, $value['recommend']);
                $targetCount += $value['targetnum'];
                $recommendCount += $value['recommend'];
            }
            //合计
            $indexSheet->setCellValueByColumnAndRow(8, $startRow, $targetCount);
            $indexSheet->setCellValueByColumnAndRow(9, $startRow, $recommendCount);
            $startRow++;
        }
        //设置样式
        $maxColumn = $lastColumn = PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($startColumn);
        $indexSheet->getStyle('H1:' . $maxColumn . $startRow)->applyFromArray($styleArray);

        $writer = IOFactory::createWriter($this->workbook, 'Xlsx');
        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
        }, '推荐量报表数据.xlsx', ['Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8']);
    }

    /**
     * Notes:导出月报表
     * @param $data
     * @param $oprIds
     * @return \EchoPHP\Http\Response\StreamedResponse
     * @throws PhpSpreadsheet\Exception
     * @throws PhpSpreadsheet\Reader\Exception
     * @throws PhpSpreadsheet\Writer\Exception
     */
    public function exportMonthlyRecommend($data, $oprIds){
        $this->workbook = PhpSpreadsheet\IOFactory::load($this->monthlyTemplate['path']);
        $indexSheet = $this->workbook->getSheet(0);
        //样式
        $indexSheet->getDefaultColumnDimension()->setWidth(12);
        $styleArray = [
            //居中
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
            ],
        ];

        //用户列表
        $startColumn = 9;
        $userColumnMap = [];
        foreach ($data['userlist'] as $user){
            if (!in_array($user['userid'], $oprIds)){
                continue;
            }
            $indexSheet->setCellValueExplicitByColumnAndRow($startColumn, 1, $user['cname'], DataType::TYPE_STRING);
            $indexSheet->setCellValueByColumnAndRow($startColumn, 2, '推荐量');
            $userColumnMap[$user['userid']] = $startColumn;
            $startColumn ++;
        }

        //数据填充
        $startRow = 3;
        foreach ($data['monthlyreport'] as $item){
            $recommendCount = 0;
            $date = $item['date'];
            $indexSheet->setCellValueByColumnAndRow(1, $startRow, $date);
            $indexSheet->setCellValueByColumnAndRow(2, $startRow, $item['caseid']);
            $indexSheet->setCellValueExplicitByColumnAndRow(3, $startRow, $item['casename'], DataType::TYPE_STRING);
            $indexSheet->setCellValueExplicitByColumnAndRow(4, $startRow, $this->chargewayDict[$item['chargeway']], DataType::TYPE_STRING);
            $indexSheet->setCellValueByColumnAndRow(5, $startRow, $item['vacancy']);
            $indexSheet->setCellValueByColumnAndRow(6, $startRow, $this->salaryTypeDict[$item['salarytype']]);
            $indexSheet->setCellValueByColumnAndRow(7, $startRow, $item['completion']);

            foreach ($item['userreport'] as $value){
                if (!in_array($value['oprid'], $oprIds)){
                    continue;
                }
                $indexSheet->setCellValueByColumnAndRow($userColumnMap[$value['oprid']], $startRow, $value['recommend']);
                $recommendCount += $value['recommend'];
            }
            //合计
            $indexSheet->setCellValueByColumnAndRow(8, $startRow, $recommendCount);
            $startRow++;
        }
        //设置样式
        $maxColumn = $lastColumn = PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($startColumn);
        $indexSheet->getStyle('H1:' . $maxColumn . $startRow)->applyFromArray($styleArray);
        $writer = IOFactory::createWriter($this->workbook, 'Xlsx');
        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
            }, '推荐量报表数据.xlsx', ['Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=UTF-8']);
    }
}